package com.xspace.android.imcommon.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.text.TextUtils;
import android.util.Log;

import com.xspace.android.imcommon.entity.Friend;
import com.xspace.android.xafcommon.util.HypyUtil;
import com.xspace.android.xafcommon.util.LogUtil;
import com.xspace.android.xafcommon.util.StringUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * MessageDb数据库
 * 
 * 
 */
public class FriendDb extends BaseDb {

	public FriendDb(Context context) {
		super(context);
	}

	private Object syncObjct = new Object();
	public static final String TAG = "FriendDb";
	/**
	 * _id
	 */
	public static final String _ID = "_id";

	/**
	 * 头像，存放url
	 */
	public static final String COLOUMN_AVATAR = "avatar";

	/**
	 * 姓名
	 */
	public static final String COLOUMN_NAME = "name";

	/**
	 * 手机号码
	 */
	public static final String COLOUMN_MOBILE = "mobile";

	/**
	 * 性别 女 男 汉字区分
	 */
	public static final String COLOUMN_SEX = "sex";

	/**
	 * 地区
	 */
	public static final String COLOUMN_AREA = "area";

	/**
	 * 个性签名
	 */
	public static final String COLOUMN_QQ = "qq";

	/**
	 * 操作状态 0未添加为好友 1已添加为好友
	 */
	public static final String COLOUMN_OPERATE_STATUS = "operate_status";
	/**
	 * 插入时间
	 */
	public static final String COLOUMN_INSERT_TIME = "insert_time";
	/**
	 * 好友的barejid
	 */
	public static final String COLOUMN_BAREJID = "barejid";
	/**
	 * 好友的uuid
	 */
	public static final String COLOUMN_UUID = "uuid";
	/**
	 * 首字母
	 */
	public static final String COLOUMN_SHORTKEY = "short_key";
	/**
	 * 用户状态
	 */
	public static final String COLOUMN_STATUS = "user_status";
	/**
	 * 好友所属人
	 */
	public static final String COLOUMN_OWNER = "owner";
	public static final String TABLE_NAME = "FRIENDS";
	public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME
			+ " (" + _ID + " INTEGER primary key, " + COLOUMN_AVATAR
			+ " TEXT, " + COLOUMN_NAME + " TEXT, " + COLOUMN_MOBILE + " TEXT, "
			+ COLOUMN_SEX + " TEXT, " + COLOUMN_AREA + " TEXT, " + COLOUMN_QQ
			+ " TEXT, " + COLOUMN_OPERATE_STATUS + " INTEGER, "
			+ COLOUMN_INSERT_TIME + " TEXT, " + COLOUMN_BAREJID + " TEXT, "
			+ COLOUMN_UUID + " TEXT, " + COLOUMN_OWNER + " TEXT,"
			+ COLOUMN_SHORTKEY + " TEXT," + COLOUMN_STATUS + " INTEGER)";

	@Override
	String getTableName() {
		return TABLE_NAME;
	}

	/**
	 * 保存消息数据
	 * 
	 * @return
	 */
	public int insertOrUpdateMsg(Friend friendBean, String owner) {
		synchronized (syncObjct) {
			ContentValues values = new ContentValues();
			if (!StringUtils.isNull(friendBean.mArea))
				values.put(COLOUMN_AREA, friendBean.mArea);
			if (!TextUtils.isEmpty(friendBean.mAvatar))
				values.put(COLOUMN_AVATAR, friendBean.mAvatar);
			if (!StringUtils.isNull(friendBean.mBarejid)){
				values.put(COLOUMN_BAREJID, StringUtils.parseBareAddress(friendBean.mBarejid));
			}
			if (!StringUtils.isNull(friendBean.mInsertTime))
				values.put(COLOUMN_INSERT_TIME, friendBean.mInsertTime);
			if (!TextUtils.isEmpty(friendBean.mMobile)
					&& !"null".equals(friendBean.mMobile))
				values.put(COLOUMN_MOBILE, friendBean.mMobile);
			if (!StringUtils.isNull(friendBean.mName)) {
				values.put(COLOUMN_NAME, friendBean.mName);
				try {
					if (friendBean.mName != null)
						friendBean.shotKey = HypyUtil.cn2py(friendBean.mName);
				} catch (Exception e) {
					e.printStackTrace();
				}
				values.put(COLOUMN_SHORTKEY, friendBean.shotKey);
			}
			if (friendBean.mOperateStatus >= 0)
				values.put(COLOUMN_OPERATE_STATUS, friendBean.mOperateStatus);
			if (!StringUtils.isNull(owner))
				values.put(COLOUMN_OWNER, owner);
			if (!StringUtils.isNull(friendBean.mQQ))
				values.put(COLOUMN_QQ, friendBean.mQQ);
			if (!StringUtils.isNull(friendBean.mSex))
				values.put(COLOUMN_SEX, friendBean.mSex);
			if (!StringUtils.isNull(friendBean.mUUID))
				values.put(COLOUMN_UUID, friendBean.mUUID);
			if (friendBean.mStatus >= 0)
				values.put(COLOUMN_STATUS, friendBean.mStatus);
			String sql = "select * from " + TABLE_NAME + " where "
					+ COLOUMN_BAREJID + " = '" + friendBean.mBarejid + "' and "
					+ COLOUMN_OWNER + " = '" + owner + "'";
			Cursor cursor = null;
			try {
				long rowId = -1;
				checkDb();
				cursor = db.rawQuery(sql, null);
				if (cursor != null && cursor.getCount() > 0) {
					rowId = db.update(TABLE_NAME, values, COLOUMN_BAREJID
							+ "=? and " + COLOUMN_OWNER + "=?", new String[] {
							friendBean.mBarejid, owner });
					Log.v("mRoster",
							"insertOrUpdateMsg >>cur_count:"
									+ cursor.getCount() + "mobile:"
									+ friendBean.mMobile + " rowid:" + rowId);
				} else {
					rowId = db.insert(TABLE_NAME, null, values);
					Log.i(TAG, "insertOrUpdateMsg()... insert rowId: " + rowId);
				}
				return (int) rowId;
			} catch (Exception e) {
				e.printStackTrace();
				return -1;
			} finally {
				closeDbAndCursor(cursor);
			}
		}
	}
    public int queryInviteCount(String owner,int status){
    	int count=0;
		String sql = "select count(*) from " + TABLE_NAME + " where " + COLOUMN_OWNER
				+ " ='" + owner + "' and " + COLOUMN_STATUS + " = "+status;
		System.out.println("=====================sql:" + sql);
		Cursor cursor = null;
		try {
			checkDb();
			cursor = db.rawQuery(sql, null);
			System.out.println("=====================cursor.getCount():"
					+ cursor.getCount());
			if (cursor != null && cursor.getCount() > 0) {
				cursor.moveToFirst();
				count=cursor.getInt(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}
		Log.d(TAG, " friendb queryInviteCount " + count);

		
    	return count;
    }
	// 修改信息状态
	public boolean doUpdateUnread(String userId, String fromUserId, int unread) {
		try {
			checkDb();
			ContentValues cv = new ContentValues();
			cv.put(COLOUMN_STATUS, unread);
			int num = db.update(TABLE_NAME, cv, COLOUMN_BAREJID + "=? and "
					+ COLOUMN_OWNER + "=?", new String[] { userId + "",
					"" + fromUserId });
			Log.d(TAG, "updateByfddHouse_id data effected number:" + num);
			if (num > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	public boolean doUpdateAllFriendsToRead(String fromUserId, int unread) {
		try {
			checkDb();
			ContentValues cv = new ContentValues();
			cv.put(COLOUMN_STATUS, unread);
			int num = db.update(TABLE_NAME, cv, COLOUMN_OWNER + "=?", new String[] {
					"" + fromUserId });
			Log.d(TAG, "doUpdateAllToFriends data effected number:" + num);
			if (num > 0) {
				return true;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	private void queryAll() {
		checkDb();
		String[] columns = { COLOUMN_MOBILE, COLOUMN_NAME, COLOUMN_BAREJID,
				COLOUMN_OWNER };
		Cursor cur = db
				.query(TABLE_NAME, columns, null, null, null, null, null);
		if (cur != null) {
			while (cur.moveToNext()) {
				String mobile = cur.getString(0);
				String name = cur.getString(1);
				String bjid = cur.getString(2);
				String owner = cur.getString(3);
				Log.v("mRoster", "queryAllFriends >>mobile:" + mobile
						+ " name:" + name + " bjid:" + bjid + " owner:" + owner);
			}
		}
		closeDbAndCursor(cur);
	}

	/**
	 * 查询数据
	 * 
	 * @param state
	 * @return
	 */
	public ArrayList<Friend> findList(String owner) {
		ArrayList<Friend> dataList = new ArrayList<Friend>();
		String sql = "select * from " + TABLE_NAME + " where " + COLOUMN_OWNER
				+ " ='" + owner + "' and " + COLOUMN_STATUS + " = 1"
				+ " order by " + COLOUMN_SHORTKEY + " asc";
		System.out.println("=====================sql:" + sql);
		Cursor cursor = null;
		try {
			checkDb();
			cursor = db.rawQuery(sql, null);
			System.out.println("=====================cursor.getCount():"
					+ cursor.getCount());
			if (cursor != null && cursor.getCount() > 0) {
				cursor.moveToFirst();
				for (int i = 0; i < cursor.getCount(); i++) {
					cursor.moveToPosition(i);
					Friend friend = new Friend();
					friend._id = cursor.getInt(cursor.getColumnIndex(_ID));
					friend.mArea = cursor.getString(cursor
							.getColumnIndex(COLOUMN_AREA));
					friend.mAvatar = cursor.getString(cursor
							.getColumnIndex(COLOUMN_AVATAR));
					friend.mBarejid = cursor.getString(cursor
							.getColumnIndex(COLOUMN_BAREJID));
					friend.mInsertTime = cursor.getString(cursor
							.getColumnIndex(COLOUMN_INSERT_TIME));
					friend.mMobile = cursor.getString(cursor
							.getColumnIndex(COLOUMN_MOBILE));
					friend.mName = cursor.getString(cursor
							.getColumnIndex(COLOUMN_NAME));
					friend.mOperateStatus = cursor.getInt(cursor
							.getColumnIndex(COLOUMN_OPERATE_STATUS));
					friend.mOwner = cursor.getString(cursor
							.getColumnIndex(COLOUMN_OWNER));
					friend.mQQ = cursor.getString(cursor
							.getColumnIndex(COLOUMN_QQ));
					friend.mSex = cursor.getString(cursor
							.getColumnIndex(COLOUMN_SEX));
					friend.mUUID = cursor.getString(cursor
							.getColumnIndex(COLOUMN_UUID));
					friend.shotKey = cursor.getString(cursor
							.getColumnIndex(COLOUMN_SHORTKEY));
					friend.mStatus = cursor.getInt(cursor
							.getColumnIndex(COLOUMN_STATUS));
					dataList.add(friend);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}
		Log.d(TAG, sql + " , and find " + dataList.size());

		return dataList;
	}
	public ArrayList<Friend> FindList(String owner,int status) {
		ArrayList<Friend> dataList = new ArrayList<Friend>();
		String sql = "select * from " + TABLE_NAME + " where " + COLOUMN_OWNER
				+ " ='" + owner + "' and " + COLOUMN_STATUS + " = "+status
				+ " order by " + COLOUMN_SHORTKEY + " asc";
		System.out.println("=====================sql:" + sql);
		Cursor cursor = null;
		try {
			checkDb();
			cursor = db.rawQuery(sql, null);
			System.out.println("=====================cursor.getCount():"
					+ cursor.getCount());
			if (cursor != null && cursor.getCount() > 0) {
				cursor.moveToFirst();
				for (int i = 0; i < cursor.getCount(); i++) {
					cursor.moveToPosition(i);
					Friend friend = new Friend();
					friend._id = cursor.getInt(cursor.getColumnIndex(_ID));
					friend.mArea = cursor.getString(cursor
							.getColumnIndex(COLOUMN_AREA));
					friend.mAvatar = cursor.getString(cursor
							.getColumnIndex(COLOUMN_AVATAR));
					friend.mBarejid = cursor.getString(cursor
							.getColumnIndex(COLOUMN_BAREJID));
					friend.mInsertTime = cursor.getString(cursor
							.getColumnIndex(COLOUMN_INSERT_TIME));
					friend.mMobile = cursor.getString(cursor
							.getColumnIndex(COLOUMN_MOBILE));
					friend.mName = cursor.getString(cursor
							.getColumnIndex(COLOUMN_NAME));
					friend.mOperateStatus = cursor.getInt(cursor
							.getColumnIndex(COLOUMN_OPERATE_STATUS));
					friend.mOwner = cursor.getString(cursor
							.getColumnIndex(COLOUMN_OWNER));
					friend.mQQ = cursor.getString(cursor
							.getColumnIndex(COLOUMN_QQ));
					friend.mSex = cursor.getString(cursor
							.getColumnIndex(COLOUMN_SEX));
					friend.mUUID = cursor.getString(cursor
							.getColumnIndex(COLOUMN_UUID));
					friend.shotKey = cursor.getString(cursor
							.getColumnIndex(COLOUMN_SHORTKEY));
					friend.mStatus = cursor.getInt(cursor
							.getColumnIndex(COLOUMN_STATUS));
					dataList.add(friend);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}
		Log.d(TAG, sql + " , and find " + dataList.size());

		return dataList;
	}
	public HashMap<String, Friend> queryFriendMap(String owner) {
		HashMap<String, Friend> friendMap = new HashMap<String, Friend>();
		String sql = "select * from " + TABLE_NAME + " where " + COLOUMN_OWNER
				+ " ='" + owner + "' order by " + COLOUMN_SHORTKEY + " asc";

		Cursor cursor = null;
		try {
			checkDb();
			cursor = db.rawQuery(sql, null);
			if (cursor != null && cursor.getCount() > 0) {
				cursor.moveToFirst();
				for (int i = 0; i < cursor.getCount(); i++) {
					cursor.moveToPosition(i);
					Friend friend = new Friend();
					friend._id = cursor.getInt(cursor.getColumnIndex(_ID));
					friend.mArea = cursor.getString(cursor
							.getColumnIndex(COLOUMN_AREA));
					friend.mAvatar = cursor.getString(cursor
							.getColumnIndex(COLOUMN_AVATAR));
					friend.mBarejid = cursor.getString(cursor
							.getColumnIndex(COLOUMN_BAREJID));
					friend.mInsertTime = cursor.getString(cursor
							.getColumnIndex(COLOUMN_INSERT_TIME));
					friend.mMobile = cursor.getString(cursor
							.getColumnIndex(COLOUMN_MOBILE));
					friend.mName = cursor.getString(cursor
							.getColumnIndex(COLOUMN_NAME));
					friend.mOperateStatus = cursor.getInt(cursor
							.getColumnIndex(COLOUMN_OPERATE_STATUS));
					friend.mOwner = cursor.getString(cursor
							.getColumnIndex(COLOUMN_OWNER));
					friend.mQQ = cursor.getString(cursor
							.getColumnIndex(COLOUMN_QQ));
					friend.mSex = cursor.getString(cursor
							.getColumnIndex(COLOUMN_SEX));
					friend.mUUID = cursor.getString(cursor
							.getColumnIndex(COLOUMN_UUID));
					friend.shotKey = cursor.getString(cursor
							.getColumnIndex(COLOUMN_SHORTKEY));
					friend.mStatus = cursor.getInt(cursor
							.getColumnIndex(COLOUMN_STATUS));
					friendMap.put(friend.mBarejid, friend);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}

		return friendMap;
	}

	/**
	 * 
	 * @Description: 查询好友
	 * @param owner
	 * @return
	 * @throws
	 */
	public Friend queryFriends(String owner, String friendsJid) {
		// Log.v("mRoster","queryFriends >> owner:"+owner+" friendsJid:"+friendsJid);
		Friend friend = new Friend();
		String sql = "select * from " + TABLE_NAME + " where " + COLOUMN_OWNER
				+ " ='" + owner + "' and " + COLOUMN_BAREJID + " = '"
				+ friendsJid + "'";

		Cursor cursor = null;
		try {
			checkDb();
			cursor = db.rawQuery(sql, null);
			if (cursor != null && cursor.getCount() > 0) {
				cursor.moveToFirst();
				friend._id = cursor.getInt(cursor.getColumnIndex(_ID));
				friend.mArea = cursor.getString(cursor
						.getColumnIndex(COLOUMN_AREA));
				friend.mAvatar = cursor.getString(cursor
						.getColumnIndex(COLOUMN_AVATAR));
				friend.mBarejid = cursor.getString(cursor
						.getColumnIndex(COLOUMN_BAREJID));
				friend.mInsertTime = cursor.getString(cursor
						.getColumnIndex(COLOUMN_INSERT_TIME));
				friend.mMobile = cursor.getString(cursor
						.getColumnIndex(COLOUMN_MOBILE));
				friend.mName = cursor.getString(cursor
						.getColumnIndex(COLOUMN_NAME));
				friend.mOperateStatus = cursor.getInt(cursor
						.getColumnIndex(COLOUMN_OPERATE_STATUS));
				friend.mOwner = cursor.getString(cursor
						.getColumnIndex(COLOUMN_OWNER));
				friend.mQQ = cursor
						.getString(cursor.getColumnIndex(COLOUMN_QQ));
				friend.mSex = cursor.getString(cursor
						.getColumnIndex(COLOUMN_SEX));
				friend.mUUID = cursor.getString(cursor
						.getColumnIndex(COLOUMN_UUID));
				friend.shotKey = cursor.getString(cursor
						.getColumnIndex(COLOUMN_SHORTKEY));
				friend.mStatus = cursor.getInt(cursor
						.getColumnIndex(COLOUMN_STATUS));
				// Log.v("mRoster","queryFriends >>cur count:"+cursor.getCount()+" name:"+friend.mName+" mobile:"+friend.mMobile);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}

		return friend;
	}

	/**
	 * 
	 * @Description: 查询好友列表
	 * @param owner
	 * @return
	 * @throws
	 */
	public List<Friend> queryLikeFriends(String owner, String value) {
		List<Friend> friends = new ArrayList<Friend>();
		String sql = "select * from " + TABLE_NAME + " where " + COLOUMN_OWNER
				+ "='" + owner + "' and " + COLOUMN_STATUS + " =1 and ("
				+ COLOUMN_NAME + " like '%" + value + "%' or " + COLOUMN_MOBILE
				+ " like '%" + value + "%') order by " + COLOUMN_SHORTKEY
				+ " asc";

		Cursor cursor = null;
		try {
			checkDb();
			cursor = db.rawQuery(sql, null);
			if (cursor != null && cursor.getCount() > 0) {
				cursor.moveToFirst();
				for (int i = 0; i < cursor.getCount(); i++) {
					cursor.moveToPosition(i);
					Friend friend = new Friend();
					friend._id = cursor.getInt(cursor.getColumnIndex(_ID));
					friend.mArea = cursor.getString(cursor
							.getColumnIndex(COLOUMN_AREA));
					friend.mAvatar = cursor.getString(cursor
							.getColumnIndex(COLOUMN_AVATAR));
					friend.mBarejid = cursor.getString(cursor
							.getColumnIndex(COLOUMN_BAREJID));
					friend.mInsertTime = cursor.getString(cursor
							.getColumnIndex(COLOUMN_INSERT_TIME));
					friend.mMobile = cursor.getString(cursor
							.getColumnIndex(COLOUMN_MOBILE));
					friend.mName = cursor.getString(cursor
							.getColumnIndex(COLOUMN_NAME));
					friend.mOperateStatus = cursor.getInt(cursor
							.getColumnIndex(COLOUMN_OPERATE_STATUS));
					friend.mOwner = cursor.getString(cursor
							.getColumnIndex(COLOUMN_OWNER));
					friend.mQQ = cursor.getString(cursor
							.getColumnIndex(COLOUMN_QQ));
					friend.mSex = cursor.getString(cursor
							.getColumnIndex(COLOUMN_SEX));
					friend.mUUID = cursor.getString(cursor
							.getColumnIndex(COLOUMN_UUID));
					friend.shotKey = cursor.getString(cursor
							.getColumnIndex(COLOUMN_SHORTKEY));
					friend.mStatus = cursor.getInt(cursor
							.getColumnIndex(COLOUMN_STATUS));
					friends.add(friend);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}

		return friends;
	}

	/**
	 * 
	 * @Description: 查询好友
	 * @param owner
	 * @return
	 * @throws
	 */
	public boolean queryISFriend(String owner, String friendsJid) {
		boolean isFlag = false;
		String sql = "select * from " + TABLE_NAME + " where " + COLOUMN_OWNER
				+ "='" + owner + "' and " + COLOUMN_BAREJID + "='" + friendsJid
				+ "'";

		Cursor cursor = null;
		try {
			checkDb();
			cursor = db.rawQuery(sql, null);
			if (cursor != null && cursor.getCount() > 0) {
				isFlag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeDbAndCursor(cursor);
		}
		return isFlag;
	}

	public void clearAllData() {
		try {
			checkDb();
			String sql = "delete from " + TABLE_NAME + ";";
			db.execSQL(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public boolean deleteUser(String userId, String fromUserId) {
		try {
			checkDb();
			Log.i("frienddb", "delete user userid:" + userId + " fromuserid:"
					+ fromUserId);
			String whereClause = String.format(" %s = ? and %s = ? ",
					COLOUMN_BAREJID, COLOUMN_OWNER);
			String[] whereArgs = new String[] { userId, fromUserId };
			int res = db.delete(TABLE_NAME, whereClause, whereArgs);
			return res > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

}
